package Database;

##########################
#     OmnibusProxy       #
#    Krzysztof Ulman     #
# Politechnika Krakowska #
#      Kraków 2012       #
##########################

use strict;
use warnings;
use encoding 'utf8';

use DBI;
use Log::Log4perl;
use Try::Tiny;

use ProxyDto;
use SearcherDto;
use StatementDto;

our $OPERATION_INSERT = "INSERT";
our $OPERATION_UPDATE = "UPDATE";

#nazwa bazy danych
our $DB_NAME;

#uzytkownik bazy danych
our $DB_USER;

#haslo uzytkownika bazy danych
our $DB_PASSWORD;

#prefix tabel w bazie danych
our $PREFIX;

#handler polaczenia z baza - globalny dla calej aplikacji
our $conn;	
	
#handler do loggera		
our $logger;

#handler do ostatniej odpowiedzi;
my $result;

#konstruktor
sub new {
	my $self = {};
	bless $self;
	$logger = Log::Log4perl->get_logger("Database");
	return $self;
}

#ustawienie danych do polaczenia z baza
sub init {
	my $self = shift;
	$PREFIX	 = shift;
	$DB_NAME = shift;
	$DB_USER = shift;
	$DB_PASSWORD = shift;
	
	if($PREFIX eq 'undefined') {
		$PREFIX = '';
	}
}

#laczenie sie z baza danych
sub connect {
	my $self = shift;
	if(!$conn) {
		$conn = DBI->connect("DBI:Pg:dbname=$DB_NAME", $DB_USER, $DB_PASSWORD, {pg_enable_utf8 => 1});
		if($conn) {
			if(AppConfig->getProperty('DBI_LOG_FILE')) {
				DBI->trace(1, AppConfig->getProperty('DBI_LOG_FILE'));
			}
			$logger->info("Polaczono z baza danych");
		} else {
			$logger->fatal('Nie mozna polaczyc sie z baza danych');
		}
	} else {
		my $mess = 'Ponowne laczenie z baza danych odrzucone';
		$logger->warn($mess);
	}
}

#rozlaczanie z baza danych
sub disconnect {
	my $self = shift;
	try {
		while($self->getRow()) {
			#pobranie wszystkiego z bufora przed rozlaczeniem
		}
		sleep(1);
		$conn->disconnect();
	} catch {
		my $e = $_;
		Screen->printAndLogFatal($e);
		try {
			$conn->disconnect();
		} catch {
			my $e = $_;
			Screen->printAndLogFatal($e);	
		};
	};
}

#wykonanie zapytania do bazy
sub executeQuery {
	if(@_ == 2) {
		my $self = shift;
		$self->{result} = $conn->prepare($_[@_ - 1]);
		$self->{result}->execute();
	} else {
		$logger->fatal('Nieprawidlowa liczba argumentow podczas wykonywania zapytania do bazy');
	}
}

#zwraca kolejny rekord odpowiedzi
sub getRow {
	my $self = shift;
	if($self->{result}) {
		return $self->{result}->fetchrow();
	} else {
		return 0;
	}
}

#zwraca ID ostatnio dodanego rekordu
sub getLastInsertId {
	if(@_ == 3) {
		my $self = shift;
	} 
	my $table = shift;
	my $table_id = shift;
	return $conn->last_insert_id(undef, undef, $table, $table_id);
}

sub getDbDateTime {
	my $self = shift;
	$self->executeQuery("select current_timestamp(0) as timestamp;");
	(my $timestamp) = $self->getRow();
	return $timestamp;
}

########################################

#zwraca cala zawartosc tabeli config
sub selectConfig {
	my $self = shift;
	$self->executeQuery("SELECT name, value FROM \"" . $PREFIX . "config\" WHERE config_type!='PASS' AND config_type!='WORK';");
}

sub selectSearchersConfig {
	my $self = shift;
	$self->executeQuery("SELECT searcher_id, name, value FROM\"" . $PREFIX . "searchers_config\";");
}

#zwraca informacje w jakim stanie jest aplikacja
sub selectCrawlerIsWorking {
	my $self = shift;
	$self->executeQuery("SELECT value FROM \"" . $PREFIX . "config\" WHERE name='CRAWLER_IS_WORKING' AND config_type='WORK';");
	(my $value) = $self->getRow();
	if($value) {
		return $value;
	} else {
		return undef;
	}
}

### OBSLUGA STANU APLIKACJI ###

sub updateApplicationState {
	my $self = shift;
	my $value = shift;
	$self->executeQuery("UPDATE \"" . $PREFIX . "config\" SET value='$value' WHERE name='CRAWLER_IS_WORKING' AND config_type='WORK';");
#	if($value eq 'YES') {
#		$self->executeQuery("UPDATE \"" . $PREFIX . "config\" SET value='$value' WHERE name='CRAWLER_IS_WORKING' AND config_type='WORK' AND value='NO';");
#	} elsif($value eq 'NO') {
#		$self->executeQuery("UPDATE \"" . $PREFIX . "config\" SET value='$value' WHERE name='CRAWLER_IS_WORKING' AND config_type='WORK' AND value='SHUTTING_DOWN';");
#	} elsif($value eq 'SHUTTING_DOWN') {
#		$self->executeQuery("UPDATE \"" . $PREFIX . "config\" SET value='$value' WHERE name='CRAWLER_IS_WORKING' AND config_type='WORK' AND value='YES';");
#	}
}

### OBSLUGA PROXY ###

#zwraca zawartosc tabeli proxies
sub selectAllProxies {
	my $self = shift;
	$self->executeQuery("SELECT proxy_id, address, port, state, hides_real_ip, last_checked, info FROM \"" . $PREFIX . "proxies\" ORDER BY RANDOM();");
}

#zwraca zawartosc tabeli proxies
sub selectNotCheckedProxies {
	my $self = shift;
	$self->executeQuery("SELECT proxy_id, address, port, state, hides_real_ip, last_checked, info FROM \"" . $PREFIX . "proxies\" WHERE state IS NULL ORDER BY RANDOM();");
}

#zwraca tylko oznaczone jako dzialajace
sub selectGoodProxies {
	my $self = shift;
	$self->executeQuery("SELECT proxy_id, address, port, state, hides_real_ip, last_checked, info FROM \"" . $PREFIX . "proxies\" WHERE state='OK' ORDER BY RANDOM();");
}

#aktualizuje status wskazanego proxy
sub updateProxyState {
	my $self = shift;
	my $proxyId = shift;
	my $proxyState = shift;
	my $proxyHidesRealIp = shift;
	$self->executeQuery("UPDATE \"" . $PREFIX . "proxies\" SET state='" . $proxyState . "', hides_real_ip='" . $proxyHidesRealIp . "', last_checked='NOW()' WHERE proxy_id=" . $proxyId . ";");
}

#zapisuje dane do tabeli OMNIBUS_proxies_state
sub saveProxiesState {
	my $self = shift;
	my $proxyDto = shift;
	my $searcherDto = shift;
	my $state = shift;;
	
	#sprawdzamy czy juz jest taki rekord
	$self->executeQuery("SELECT state FROM \"" . $PREFIX . "proxies_state\" WHERE proxy_id=" . $proxyDto->getId() . " AND searcher_id=" . $searcherDto->getSearcherId() . ";");
	my $operation = $OPERATION_INSERT;
	while(my $state = $self->getRow()) {
		$operation = $OPERATION_UPDATE;
	}
	
	#jezeli jest to robimy update, a jesli nie to insert do bazy
	if($operation eq $OPERATION_INSERT) {
		$self->executeQuery("INSERT INTO \"" . $PREFIX . "proxies_state\"(\"proxy_id\", \"searcher_id\", \"state\", \"last_checked\") VALUES(". $proxyDto->getId() .", " . $searcherDto->getSearcherId() .", '" . $state . "', NOW())");
	} else {
		$self->executeQuery("UPDATE \"" . $PREFIX . "\"proxies_state SET state='" . $state . "', last_cheked=NOW() WHERE proxy_id=" . $proxyDto->getId() . " AND searcher_id=" . $searcherDto->getSearcherId());
	}
}

### OBSLUGA WYSZUKIWAREK ###

#pobiera i zwraca liste wszystkich zdefiniowanyc wyszukiwarek
sub getSearchers {
	my $self = shift;
	my $getAll = shift;
	my $sql = "SELECT searcher_id, name, url, active FROM \"" . $PREFIX . "searchers\"";
	if(!$getAll) {
		$sql .= " WHERE active=1";
	}
	$self->executeQuery($sql);
	
	my @searchers;
	while((my $searcherId, my $name, my $url, my $active) = $self->getRow()) {
		my $searcherDto = SearcherDto->new();
		$searcherDto->setSearcherId($searcherId);
		$searcherDto->setName($name);
		$searcherDto->setUrl($url);
		$searcherDto->setActive($active);
		push(@searchers, $searcherDto);
	}
	
	return \@searchers;
}

### OBSLUGA STATEMENTOW ###

sub getStatementsNumber {
	my $self = shift;
	$self->executeQuery("SELECT count(new_statement_id) FROM \"" . $PREFIX . "new_statements\"");
	(my $statementNum) = $self->getRow();
	if($statementNum) {
		return $statementNum;
	} else {
		return 0;
	}
}

sub getStatementsPack {
	my $self = shift;
	my $packSize = shift;
	my @statementsPack;
	#pobierz nowe
	$self->executeQuery("SELECT new_statement_id, statement_id, statement FROM \"" . $PREFIX . "new_statements\" ORDER BY new_statement_id LIMIT " . $packSize);
	while((my $newStatementId, my $statementId, my $statement) = $self->getRow()) {
		if(!$statementId && $statement) {
			#calkowicie nowa fraza
			#wpisujemy ja do tabeli statements
			my $db = Database->new();
			$db->executeQuery("INSERT INTO \"" . $PREFIX . "statements\"(statement) VALUES('$statement');");
			my $statementId = $db->getLastInsertId($PREFIX . 'statements', 'statement_id');
			my $sDto = StatementDto->new();
			$sDto->setNewStatementId($newStatementId);
			$sDto->setStatementId($statementId);
			$sDto->setStatement($statement);
			push(@statementsPack, $sDto);
		} elsif($statementId) {
			#mamy juz ta fraze w tabeli statements, wiec zbieramy o niej informacje jeszcze raz
			my $db = Database->new();
			$db->executeQuery("SELECT statement_id, statement, tokens_num FROM \"" . $PREFIX . "statements\" WHERE statement_id=$statementId;");
			(my $statementId, my $statement, my $tokensNum) = $db->getRow();
			my $sDto = StatementDto->new();
			$sDto->setStatementId($statementId);
			$sDto->setStatement($statement);
			$sDto->setTokensNum($tokensNum);
			push(@statementsPack, $sDto);
		}
	}
	return \@statementsPack;
}

#usuwa rekord z new_statements
sub removeNewStatement {
	my $self = shift;
	my $newStatementId = shift;
	$self->executeQuery("DELETE FROM \"" . $PREFIX . "new_statements\" WHERE new_statement_id=$newStatementId");
}

#aktualizuje dane w tabeli statements
sub updateStatementAfterCollect {
	my $self = shift;
	my $statementId = shift;
	my $tokensNum = shift;
	
	my $sql = "UPDATE \"" . $PREFIX . "statements\" SET last_crawled='NOW()'";
	if($tokensNum) {
		$sql .= ", tokens_num=$tokensNum";
	}
	$sql .= " WHERE statement_id=$statementId;";
	
	$self->executeQuery($sql);
}

#wstawia rekord do tabeli statements_tokens, o ile taki juz nie istnieje
sub joinStatementWithToken {
	my $self = shift;
	my $statementId = shift;
	my $tokenId = shift;
	my $statementPos = shift;
	$self->executeQuery("SELECT count(*) as liczba FROM \"" . $PREFIX . "statements_tokens\" WHERE statement_id=$statementId AND token_id=$tokenId;");
	(my $liczba) = $self->getRow();
	if(!$liczba) { 
		$self->executeQuery("INSERT INTO \"" . $PREFIX . "statements_tokens\"(statement_id, token_id, statement_pos) VALUES($statementId, $tokenId, $statementPos)");
	}
}

#pobiera token z bazy
sub selectTokenId {
	my $self = shift;
	my $token = shift;
	$self->executeQuery("SELECT token_id FROM \"" . $PREFIX . "tokens\" WHERE token='$token'");
	(my $tokenId) = $self->getRow();
	return $tokenId;
}

#dodaje nowy token do bazy
sub insertToken {
	my $self = shift;
	my $token = shift;
	$self->executeQuery("INSERT INTO \"" . $PREFIX . "tokens\"(token) VALUES('$token');");
	return getLastInsertId($PREFIX . 'tokens', 'token_id');
}

#sprawdza czy podana fraza jest juz w bazie danych i zwraca Id
sub statementExistsId {
	my $self = shift;
	my $statement = shift;
	$self->executeQuery("SELECT count(new_statement_id) FROM \"" . $PREFIX . "new_statements\" WHERE statement='$statement';");
	(my $result) = $self->getRow();
	if($result >= 1) {
		$self->executeQuery("SELECT new_statement_id FROM \"" . $PREFIX . "new_statements\" WHERE statement='$statement';");
		($result) = $self->getRow();
		return $result;
	} else {
		$self->executeQuery("SELECT count(statement_id) FROM \"" . $PREFIX . "statements\" WHERE statement='$statement';");
		($result) = $self->getRow();
		if($result >= 1) {
			$self->executeQuery("SELECT statement_id FROM \"" . $PREFIX . "statements\" WHERE statement='$statement';");
			($result) = $self->getRow();
			return $result;
		}
	}
	return 0;
}

#wstawia fraze do bazy danych
sub insertStatement {
	my $self = shift;
	my $statement = shift;
	my $statementId = shift;
	if($statementId && !$statement) {
		$self->executeQuery("INSERT INTO \"" . $PREFIX . "new_statements\"(statement_id, statement, insert_timestamp) VALUES($statementId, NULL, 'NOW()')");
	} else {
		$self->executeQuery("INSERT INTO \"" . $PREFIX . "new_statements\"(statement_id, statement, insert_timestamp) VALUES(NULL, '$statement', 'NOW()')");
	}
}

#zwraca ID slowa lematyzowanego (jezeli znaleziono)
sub getLematWordIds {
	my $self = shift;
	my $word = shift;
	$self->executeQuery("SELECT word_id FROM \"" . $PREFIX . "lematyzator\" WHERE word='$word';");
	my @results;
	while((my $wordId) = $self->getRow()) {
		push(@results, $wordId);
	}
	return \@results;
}

#wstawia powiazanie slowa z lematem
sub insertTokensLemats {
	my $self = shift;
	my $tokenId = shift;
	my $lematWordId = shift;
	$self->executeQuery("SELECT count(*) FROM \"" . $PREFIX . "tokens_lemats\" WHERE token_id=$tokenId AND workd_id=$lematWordId;");
	(my $result) = $self->getRow();
	if(!$result) {
		#wstawiamy, jezeli nie ma jeszcze w tabeli
		$self->executeQuery("INSERT INTO \"" . $PREFIX . "tokens_lemats\"(token_id, word_id) VALUES($tokenId, $lematWordId)");
	}
}

### OBSLUGA RESULTOW ###

#zwraca ID szukanego adresu URL
sub selectUrlAddrId {
	my $self = shift;
	my $urlStr = shift;
	$self->executeQuery("SELECT addr_id FROM \"" . $PREFIX . "url_addrs\" WHERE url='$urlStr';");
	(my $id) = $self->getRow();
	return $id;
}

#wstawia nowy adres URL i zwraca jego ID
sub insertUrlAddr {
	my $self = shift;
	my $urlStr = shift;
	my $title = shift;
#	#usuniecie znakow specjalnych, zeby sie nie wywalalo podczas zapisu
#	$title =~ s/'/\\'/g; 
	#4.1.2.2. Dollar-Quoted String Constants
	$self->executeQuery("INSERT INTO \"" . $PREFIX . "url_addrs\"(url, title) VALUES('$urlStr', " . '$$' . $title . '$$' . ");");
	return getLastInsertId($PREFIX . 'url_addrs', 'addr_id');
}

#aktualizuje adres URL w bazie danych
sub updateUrlAddr {
	my $self = shift;
	my $addrId = shift;
	my $url = shift;
	$self->executeQuery("UPDATE \"" . $PREFIX . "url_addrs\" SET url='$url' WHERE addr_id=$addrId;");
}

#pobiera obiekt URL z bazy
sub selectUrlId {
	my $self = shift;
	my $statementId = shift;
	my $addrId = shift;
	$self->executeQuery("SELECT url_id FROM \"" . $PREFIX . "urls\" WHERE statement_id=$statementId AND addr_id=$addrId;");
	(my $id) = $self->getRow();
	return $id;
}

#dodaje nowy obiekt URL do bazy
sub insertUrl {
	my $self = shift;
	my $statementId = shift;
	my $urlAddrId = shift;
	$self->executeQuery("INSERT INTO \"" . $PREFIX . "urls\"(statement_id, addr_id) VALUES($statementId, $urlAddrId)");
	return getLastInsertId($PREFIX . 'urls', 'url_id');
}

#dodanie rekordu do tabeli results
sub insertResult {
	my $self = shift;
	my $resultDto = shift;
	my $urlId = shift;
	my $searcherId = shift;
	my $shouldUpdate = shift;

	#TODO wylaczone!
#	if($shouldUpdate) {
#		#poprzednie oznaczamy jako stare	
#		$self->executeQuery("UPDATE \"" . $PREFIX . "results\" SET the_newest=0 WHERE the_newest=1 AND searcher_id=$searcherId AND url_id=$urlId;");
#	}

	#zapisujemy nowe
	my $position = $resultDto->getPosition();	
	$self->executeQuery("INSERT INTO \"" . $PREFIX . "results\"(searcher_id, url_id, position, timestamp, the_newest) VALUES($searcherId, $urlId, $position, 'NOW()', 1);");
}

#oznaczenie rekordow jako nieaktualne
sub markResultsNotActual {
	my $self = shift;
	my $statementId = shift;
	$self->executeQuery("UPDATE \"" . $PREFIX . "results\" as r SET the_newest=0 FROM \"" . $PREFIX . "urls\" as u WHERE u.url_id=r.url_id AND u.statement_id=$statementId AND r.the_newest=1;");
}

#zapisuje informacje o bledzie podczas pobierania danych dla okreslonej frazy
sub storeErrorMessage {
	my $self = shift;
	my $message = shift;
	my $statementId = shift;
#	#usuniecie znakow specjalnych, zeby sie nie wywalalo podczas zapisu
#	$message =~ s/'/\\'/g;
	#4.1.2.2. Dollar-Quoted String Constants
	$self->executeQuery("UPDATE \"" . $PREFIX . "statements\" SET error_message=concat_ws(' | ', error_message, " . '$$' . $message . '$$' . ") WHERE statement_id=$statementId;");
}

### OBSLUGA GLOBAL RANK ###

sub selectUrlsByStatementId {
	my $self = shift;
	my $statementId = shift;
	$self->executeQuery("SELECT url_id FROM \"" . $PREFIX . "urls\" WHERE statement_id=$statementId");
	my @urls;
	while((my $urlId) = $self->getRow()) {
		push(@urls, $urlId);
	}
	return \@urls;
}

sub updateGlobalRankByUrlId {
	my $self = shift;
	my $urlId = shift;
	my $globalRank = shift;
	$self->executeQuery("UPDATE \"" . $PREFIX . "urls\" SET global_rank=$globalRank WHERE url_id=$urlId");
}

sub selectPositionsByUrlId {
	my $self = shift;
	my $urlId = shift;
	$self->executeQuery("SELECT searcher_id, position FROM \"" . $PREFIX . "results\" WHERE url_id=$urlId AND the_newest=1;");
	my %map;
	while((my $searcherId, my $position) = $self->getRow()) {
		#ma byc jeden wynik z jednej wyszukiwarki - jezeli jest wiecej, to wybieramy ten lepszy
		if($map{$searcherId}) {
			if($position < $map{$searcherId}) {
				$map{$searcherId} = $position;
			}
		} else {
			$map{$searcherId} = $position;
		}
	}
	
	my @positions;
	foreach my $key (keys(%map)) {
		push(@positions, $map{$key});
	}
	return \@positions;
}

1;